using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;

/// <summary>
/// Summary description for Product
/// </summary>
public class ProductDAO : DBConnection
{
    #region sql command
    string SQL_SELECT_ID = "";
    string SQL_SELECT_ALL = "select * from Product";
    string SQL_SELECT_BY_ID = "select * from product where ProductID=@id";
    string SQL_SELECT_BY_PRICE = "";
    string SQL_INSERT = "insert into Product(ProductCategoryID, ProductName, PetCategoryId, Unit, Size, Quantity, Price, ImageURL, ItemTypeID, Info,ModifiedDate) values(@productCategoryId, @productName, @petCategoryId, @unit, @size, @quantity, @price, @imageURL, @itemTypeID, @info,@modifiedDate)";
    string SQL_UPDATE = "update Product set ProductCategoryID = @productCategoryId, ProductName = @productName, PetCategoryId = @PetCategoryId, Unit = @unit, Size = @size, Quantity = @quantity, Price = @price,ImageURL= @imageURL, Info=@info,ModifiedDate=@ModifiedDate where ProductID = @productId";
    string SQL_DELETE = "delete from Product where ProductID = @productId";
    string SQL_UPDATE_SALE_OFF_FOR_PRO = "update Product set SaleOff=@saleOff where ProductID=@productId";
    #endregion

    #region fields
    string message = "";
    #endregion

    #region constructors
    public ProductDAO()
    {
    }
    #endregion

    #region properties

    public string Message
    {
        get { return message; }
        set { message = value; }
    }

    #endregion

    #region methods
    public Product GetProduct(int id)
    {
        Product pro = new Product();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_BY_ID, conn);
            if (Connect())
            {
                command.Parameters.AddWithValue("@id",id);
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {               
                    pro.ID = dr.GetInt32(0);
                    pro.Category.ID = dr.GetInt32(1);
                    pro.Name = dr.GetString(2);
                    pro.ForCategoryOfPet.ID = dr.GetInt32(3);
                    pro.Unit = dr.GetString(4);
                    pro.Size = dr.GetString(5);
                    pro.Quantity = dr.GetInt32(6);
                    pro.Price = dr.GetDecimal(7);
                    pro.ImageURL = dr.GetString(8);
                    pro.TypeID = dr.GetInt32(9);
                    pro.Information = dr.GetString(10);
               
                }
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return pro;
    }
    public IList<Product> GetAllProducts()
    {
        IList<Product> list = new List<Product>();
        try
        {
            SqlCommand command = new SqlCommand(SQL_SELECT_ALL, conn);
            if (Connect())
            {
                SqlDataReader dr=command.ExecuteReader();
                while(dr.Read()){
                Product pro = new Product();
                pro.ID = dr.GetInt32(0);
                pro.Category.ID = dr.GetInt32(1);
                pro.Name = dr.GetString(2);
                pro.ForCategoryOfPet.ID = dr.GetInt32(3);
                pro.Unit = dr.GetString(4);
                pro.Size = dr.GetString(5);
                pro.Quantity = dr.GetInt32(6);
                pro.Price = dr.GetDecimal(7);
                pro.ImageURL = dr.GetString(8);
                pro.TypeID = dr.GetInt32(9);
                pro.Information = dr.GetString(10);
                list.Add(pro);
                }
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return list;
    }
    public IList<Product> GetProductByCategory(int currentPage, int pageSize, int categoryID, ref int total)
    {
        IList<Product> list = new List<Product>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectProductsByCategory", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@categoryID", SqlDbType.Int);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@categoryID"].Value = categoryID;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Product product = new Product();
                    product.ID = dr.GetInt32(1);
                    product.Category.ID = categoryID;
                    product.Name = dr.GetString(3);
                    product.ForCategoryOfPet.ID = dr.GetInt32(4);
                    product.Unit = dr.GetString(5);
                    product.Size = dr.GetString(6);
                    product.Quantity = dr.GetInt32(7);
                    product.Price = dr.GetDecimal(8);
                    product.ImageURL = dr.GetString(9);
                    product.TypeID = dr.GetInt32(10);
                    product.Information = dr.GetString(11);
                    if(!dr.IsDBNull(12))product.SaleOff = (float)dr.GetDouble(12);
                    if (!dr.IsDBNull(13)) product.ModifiedDate = dr.GetDateTime(13);
                    list.Add(product);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }

    public IList<Product> GetProductByName(int currentPage, int pageSize, string name,decimal lowPrice, decimal highPrice ,ref int total)
    {
        IList<Product> list = new List<Product>();
        try
        {
            SqlCommand command = new SqlCommand("sp_SelectProductsByName", conn);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@pagesize", SqlDbType.Int);
            command.Parameters.Add("@currentpage", SqlDbType.Int);
            command.Parameters.Add("@name", SqlDbType.Int);
            command.Parameters.Add("@lowPrice", SqlDbType.Money);
            command.Parameters.Add("@highPrice", SqlDbType.Money);
            command.Parameters.Add("@totals", SqlDbType.Int);
            command.Parameters["@pagesize"].Value = pageSize;
            command.Parameters["@currentpage"].Value = currentPage;
            command.Parameters["@name"].Value ="%"+ name+"%";
            command.Parameters["@lowPrice"].Value = lowPrice;
            command.Parameters["@highPrice"].Value = highPrice;
            command.Parameters["@totals"].Direction = ParameterDirection.Output;
            command.Parameters["@totals"].Value = total;
            if (Connect())
            {
                SqlDataReader dr = command.ExecuteReader();
                while (dr.Read())
                {
                    Product product = new Product();
                    product.ID = dr.GetInt32(1);
                    product.Category.ID = dr.GetInt32(2);
                    product.Name = dr.GetString(3);
                    product.ForCategoryOfPet.ID = dr.GetInt32(4);
                    product.Unit = dr.GetString(5);
                    product.Size = dr.GetString(6);
                    product.Quantity = dr.GetInt32(7);
                    product.Price = dr.GetDecimal(8);
                    product.ImageURL = dr.GetString(9);
                    product.TypeID = dr.GetInt32(10);
                    product.Information = dr.GetString(11);
                    if (!dr.IsDBNull(12)) product.SaleOff = (float)dr.GetDouble(12);
                    if (!dr.IsDBNull(13)) product.ModifiedDate = dr.GetDateTime(13);
                    list.Add(product);
                }
                dr.Close();
                total = int.Parse(command.Parameters["@totals"].Value.ToString());
            }
        }
        catch (SqlException ex) { }
        catch (Exception ex) { }
        return list;
    }
    public int InsertProduct(Product product)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_INSERT, conn);
            cmd.Parameters.AddWithValue("@productCategoryId", product.Category.ID);
            cmd.Parameters.AddWithValue("@productName", product.Name);
            cmd.Parameters.AddWithValue("@petCategoryId", product.ForCategoryOfPet.ID);
            cmd.Parameters.AddWithValue("@unit", product.Unit);
            cmd.Parameters.AddWithValue("@size", product.Size);
            cmd.Parameters.AddWithValue("@quantity", product.Quantity);
            cmd.Parameters.AddWithValue("@price", product.Price);
            cmd.Parameters.AddWithValue("@imageURL", product.ImageURL);
            cmd.Parameters.AddWithValue("@itemTypeId", product.TypeID);
            cmd.Parameters.AddWithValue("@info",product.Information);
            cmd.Parameters.AddWithValue("@modifiedDate", product.ModifiedDate);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int UpdateProduct(Product product)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE, conn);
            cmd.Parameters.AddWithValue("@productId", product.ID);
            cmd.Parameters.AddWithValue("@productCategoryId", product.Category.ID);
            cmd.Parameters.AddWithValue("@productName", product.Name);
            cmd.Parameters.AddWithValue("@petCategoryId", product.ForCategoryOfPet.ID);
            cmd.Parameters.AddWithValue("@unit", product.Unit);
            cmd.Parameters.AddWithValue("@size", product.Size);
            cmd.Parameters.AddWithValue("@quantity", product.Quantity);
            cmd.Parameters.AddWithValue("@price", product.Price);
            cmd.Parameters.AddWithValue("@imageURL", product.ImageURL);
            cmd.Parameters.AddWithValue("@info", product.Information);
            cmd.Parameters.AddWithValue("@modifiedDate", product.ModifiedDate);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }

    public int DeleteProduct(Product product)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_DELETE, conn);
            cmd.Parameters.AddWithValue("@productId", product.ID);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }

        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }
    public int UpdateSaleOff(Product product)
    {
        int effectedRow = 0;
        try
        {
            SqlCommand cmd = new SqlCommand(SQL_UPDATE_SALE_OFF_FOR_PRO, conn);
            cmd.Parameters.AddWithValue("@saleOff", product.SaleOff);
            cmd.Parameters.AddWithValue("@productID", product.ID);
            if (Connect())
            {
                effectedRow = cmd.ExecuteNonQuery();
                Disconnect();
            }
        }
        catch (SqlException ex) { message = ex.Message; }
        catch (Exception ex) { message = ex.Message; }
        return effectedRow;
    }
    #endregion
}
